﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using MySql.Data.MySqlClient;
using System.Windows.Forms;
using System.Security.Cryptography;
using loginSQL.Pages;

namespace loginSQL.SQLHelper
{
    public class DBHelper
    {        
        public const int FILTER_ALL = 0;
        public const int FILTER_NAME = 1;
        public const int FILTER_EMAIL = 2;
        public const int FILTER_BIRTHPLACE = 3;
        public const int FILTER_NATIONALITY = 4;
        public const int FILTER_NIP = 5;
        public const int FILTER_IDCARD = 6;
        public const int FILTER_DOCFILE = 7;
        public const int FILTER_INFO = 8;
        public const int FILTER_USERDOC = 9;
        public const int FILTER_NOTE = 10;

        public const int Perpanjangan_Index = 11;
        public const int Klasifikasi_Index = 12;
        public const int Rating_Perpanjangan_Index = 13;
        public const int Catatan_Index = 14;

        private MySqlConnection connection;
        private string server;
        private string database;
        private string username;
        private string password;
        private string port;

        public DBHelper()
        {
            Initialize();
        }

        private void Initialize()
        {
            server = Constants.serverName;
            database = Constants.databaseName;
            username = Constants.usernameSQL;
            password = Constants.passwordSQL;
            port = Constants.port;
            string connectionString = "SERVER=" + server + ";" +"PORT="+port+";"+
                "DATABASE=" + database + ";" + "UID=" + username + ";" +
                "PASSWORD=" + password + ";";
            try
            {
                connection = new MySqlConnection(connectionString);
            }
            catch (Exception e)
            {
                MessageBox.Show("MySql Setting Error");
            }
        }

        public static bool isDatabaseAvailable(MySQLSettings settings)
        {
            MySqlConnection connection;
            string database = settings.databaseName;
            string connectionString = "SERVER=" + settings.server + ";" + "PORT=" + settings.port + ";" +
                "DATABASE=" + database + ";" + "UID=" + settings.username + ";" +
                "PASSWORD=" + settings.password + ";";
            try
            {
                connection = new MySqlConnection(connectionString);
                try
                {
                    connection.Open();
                    connection.Close();
                    return true;
                }
                catch (MySqlException ex)
                {
                    switch (ex.Number)
                    {
                        case 0:
                            MessageBox.Show("Cannot connect to server.  Contact administrator");
                            break;

                        case 1045:
                            MessageBox.Show("Invalid username/password, please try again");
                            break;
                    }
                }
                
            }
            catch (Exception e)
            {
                return false;
            }
            return false;
        }
        
        private bool OpenConnection()
        {
            try
            {                         
                connection.Open();
                return true;
            }
            catch(MySqlException ex)
            {
                switch (ex.Number)
                {
                    case 0:
                        MessageBox.Show("Cannot connect to server.  Contact administrator");
                        break;

                    case 1045:
                        MessageBox.Show("Invalid username/password, please try again");
                        break;
                }
            }
            return false;
        }

        private bool CloseConnection()
        {
            try
            {
                connection.Close();
                return true;
            }
            catch (MySqlException ex)
            {
                MessageBox.Show(ex.Message);
                return false;
            }
        }
                
        public bool doLogin(string username, string password)
        {
            string sha1Password = getSHA1(password);
            string query = "SELECT * FROM user WHERE username='"+username+"' AND password='"+sha1Password+"'";
            if (this.OpenConnection())
            {
                //create command
                MySqlCommand command = new MySqlCommand(query, connection);
                //create data reader
                MySqlDataReader dataReader = command.ExecuteReader();
                //string result = (string)command.ExecuteScalar();
                if (dataReader.HasRows)
                {
                    CloseConnection();
                    return true;
                }
                CloseConnection();
                return false;
            }            
            return false;
        }

        private MySqlDataReader getMySqlDataReader(string query)
        {
            if (this.OpenConnection())
            {
                MySqlCommand command = new MySqlCommand(query, connection);
                MySqlDataReader dataReader = command.ExecuteReader();
                return dataReader;
            }
            return null;
        }
        
        private string getSHA1(string input)
        {            
            byte[] data = Encoding.UTF8.GetBytes(input);
            HashAlgorithm hash = new SHA1Managed();
            byte[] hashByte = hash.ComputeHash(data);
            StringBuilder hashValue = new StringBuilder();
            Array.ForEach<byte>(hashByte, delegate(byte b) { hashValue.Append(b.ToString("X2")); });            
            return hashValue.ToString().ToLower();
        }

        private List<long> getIdListFromDataReader(MySqlDataReader dataReader)
        {
            List<long> result = new List<long>();
            if (dataReader != null)
            {
                while (dataReader.Read())
                {
                    int id = (int)((long)dataReader["id"]);
                    if(!result.Contains(id))
                        result.Add((int)((long)dataReader["id"]));
                }
            }
            dataReader.Close();
            this.CloseConnection();
            return result;
        }
        
        public List<long> getSearchQueryByTableUser(string keyword)
        {
            List<long> result = new List<long>();
            string query = "SELECT user.id FROM user,nationality,identity_type" +
                " WHERE user.name LIKE '%" + keyword + "%'" +
                " OR tempat_lahir LIKE '%" + keyword + "%'" +
                " OR tgl_lahir LIKE '%" + keyword + "%'" +
                " OR alamat LIKE '%" + keyword + "%'" +
                " OR handphone LIKE '%" + keyword + "%'" +
                " OR email LIKE '%" + keyword + "%'" +
                " OR id_number LIKE '%" + keyword + "%'" +
                " OR ( nationality_id = nationality.id AND nationality.name LIKE '%" + keyword + "%')" +
                " OR ( identity_type_id = identity_type.id AND identity_type.name LIKE '%" + keyword + "%')";
            MySqlDataReader dataReader = this.getMySqlDataReader(query);
            return getIdListFromDataReader(dataReader);                
        }

        public List<long> getDataSearchQueryByTableIdentity_Type(string keyword)
        {
            List<long> result = new List<long>();
            keyword = keyword.ToUpper();
            string query = "SELECT user.id FROM user,(SELECT id from identity_type WHERE name='" + keyword + "') as A" +
                "WHERE A.id=user.identity_type_id";
            MySqlDataReader dataReader = this.getMySqlDataReader(query);
            return getIdListFromDataReader(dataReader);

        }

        public List<long> getDataSearchQueryByTableUser_Document(string keyword)
        {
            List<long> result = new List<long>();


            return result;
        }

        private List<string> getDataUser(long id,int count)
        {
            List<string> result = new List<string>();
            string query = "SELECT id,name,tempat_lahir,tgl_lahir,gender_id sex_id,alamat,"+
                "handphone,email,identity_type_id,id_number,nationality_id"+
                " FROM user WHERE id='"+id+"'";
            if (this.OpenConnection())
            {
                MySqlCommand command = new MySqlCommand(query, connection);
                MySqlDataReader dataReader = command.ExecuteReader();
                if (dataReader != null)
                {
                    while (dataReader.Read())
                    {
                        string ids = Convert.ToString((long)dataReader["id"]);
                        string name = dataReader["name"].GetType() != typeof(DBNull) ? (string)dataReader["name"] : "-";
                        string tempat_lahir = dataReader["tempat_lahir"].GetType() != typeof(DBNull) ? (string)dataReader["tempat_lahir"] : "-";
                        string tgl_lahir = dataReader["tgl_lahir"].GetType() != typeof(DBNull) ? ((DateTime)dataReader["tgl_lahir"]).ToShortDateString() : "-";
                        string sex_id = getSexGender((int)(dataReader["sex_id"].GetType() != typeof(DBNull) ? (long)dataReader["sex_id"] : 0));
                        string alamat = dataReader["alamat"].GetType() != typeof(DBNull) ? (string)dataReader["alamat"] : "-";
                        string handphone = dataReader["handphone"].GetType() != typeof(DBNull) ? (string)dataReader["handphone"] : "-";
                        string email = dataReader["email"].GetType() != typeof(DBNull) ? (string)dataReader["email"] : "-";
                        string identyity_type_id = getIDCard((int)(dataReader["identity_type_id"].GetType() != typeof(DBNull) ? (long)dataReader["identity_type_id"] : 0));
                        string id_number = dataReader["id_number"].GetType() != typeof(DBNull) ? (string)dataReader["id_number"] : "-";
                        string nationality_id = getNationality((int)(dataReader["nationality_id"].GetType() != typeof(DBNull) ? (long)dataReader["nationality_id"] : 0));
                        string[] tempString = new string[]{ name,tempat_lahir,tgl_lahir,
                        sex_id,alamat,handphone,email,identyity_type_id,id_number,nationality_id,ids};
                        List<string> temp = new List<string>();
                        temp.Add(Convert.ToString(count));
                        temp.AddRange(tempString);
                        this.CloseConnection();
                        return temp;                                       
                    }
                }
                this.CloseConnection();
            }
            return result;
        }

        public List<string> getDataUserFromIdNumber(string id, int count)
        {
            List<string> result = new List<string>();
            string query = "SELECT name,tempat_lahir,tgl_lahir,gender_id sex_id,alamat," +
                "handphone,email,identity_type_id,id_number,nationality_id" +
                " FROM user WHERE id='" + id + "'";
            if (this.OpenConnection())
            {
                MySqlCommand command = new MySqlCommand(query, connection);
                MySqlDataReader dataReader = command.ExecuteReader();
                if (dataReader != null)
                {
                    while (dataReader.Read())
                    {
                        string name = dataReader["name"].GetType() != typeof(DBNull) ? (string)dataReader["name"] : "-";
                        string tempat_lahir = dataReader["tempat_lahir"].GetType() != typeof(DBNull) ? (string)dataReader["tempat_lahir"] : "-";
                        string tgl_lahir = dataReader["tgl_lahir"].GetType() != typeof(DBNull) ? ((DateTime)dataReader["tgl_lahir"]).ToShortDateString() : "-";
                        string sex_id = getSexGender((int)(dataReader["sex_id"].GetType() != typeof(DBNull) ? (long)dataReader["sex_id"] : 0));
                        string alamat = dataReader["alamat"].GetType() != typeof(DBNull) ? (string)dataReader["alamat"] : "-";
                        string handphone = dataReader["handphone"].GetType() != typeof(DBNull) ? (string)dataReader["handphone"] : "-";
                        string email = dataReader["email"].GetType() != typeof(DBNull) ? (string)dataReader["email"] : "-";
                        string identyity_type_id = getIDCard((int)(dataReader["identity_type_id"].GetType() != typeof(DBNull) ? (long)dataReader["identity_type_id"] : 0));
                        string id_number = dataReader["id_number"].GetType() != typeof(DBNull) ? (string)dataReader["id_number"] : "-";
                        string nationality_id = getNationality((int)(dataReader["nationality_id"].GetType() != typeof(DBNull) ? (long)dataReader["nationality_id"] : 0));
                        string[] tempString = new string[]{ name,tempat_lahir,tgl_lahir,
                        sex_id,alamat,handphone,email,identyity_type_id,id_number,nationality_id};
                        List<string> temp = new List<string>();
                        temp.Add(Convert.ToString(count));
                        temp.AddRange(tempString);
                        this.CloseConnection();
                        return temp;           
                    }
                }
                this.CloseConnection();
            }
            return result;
        }

        public Dictionary<string, string> getDataUserFromId(string id)
        {
            Dictionary<string, string> result = new Dictionary<string, string>();
            string query = "SELECT name,tempat_lahir,tgl_lahir,gender_id sex_id,alamat," +
                "handphone,email,identity_type_id,id_number,nationality_id" +
                " FROM user WHERE id='" + id + "'";
            if (this.OpenConnection())
            {
                MySqlCommand command = new MySqlCommand(query, connection);
                MySqlDataReader dataReader = command.ExecuteReader();
                if (dataReader != null)
                {
                    while (dataReader.Read())
                    {
                        //nama
                        string name = dataReader["name"].GetType() != typeof(DBNull) ? (string)dataReader["name"] : "-";
                        result.Add("nama_pemegang", name);

                        //tempat tanggal lahir
                        string tempat_lahir = dataReader["tempat_lahir"].GetType() != typeof(DBNull) ? (string)dataReader["tempat_lahir"] : "-";                        
                        string tgl_lahir = dataReader["tgl_lahir"].GetType() != typeof(DBNull) ? ((DateTime)dataReader["tgl_lahir"]).ToShortDateString() : "-";
                        result.Add("tempat_tanggal_lahir", tempat_lahir+","+tgl_lahir);

                        //jenis kelamin
                        string sex_id = getSexGender((int)(dataReader["sex_id"].GetType() != typeof(DBNull) ? (long)dataReader["sex_id"] : 0));
                        result.Add("jenis_kelamin", sex_id);

                        //alamat
                        string alamat = dataReader["alamat"].GetType() != typeof(DBNull) ? (string)dataReader["alamat"] : "-";
                        result.Add("alamat", alamat);

                        //handphone
                        string handphone = dataReader["handphone"].GetType() != typeof(DBNull) ? (string)dataReader["handphone"] : "-";
                        result.Add("handphone", handphone);

                        //email
                        string email = dataReader["email"].GetType() != typeof(DBNull) ? (string)dataReader["email"] : "-";
                        result.Add("email", email);

                        //tanda_pengenal
                        string identyity_type_id = getIDCard((int)(dataReader["identity_type_id"].GetType() != typeof(DBNull) ? (long)dataReader["identity_type_id"] : 0));
                        result.Add("tanda_pengenal", identyity_type_id);

                        //no
                        string id_number = dataReader["id_number"].GetType() != typeof(DBNull) ? (string)dataReader["id_number"] : "-";
                        result.Add("nomor", id_number);

                        //kebangsaan
                        string nationality_id = getNationality((int)(dataReader["nationality_id"].GetType() != typeof(DBNull) ? (long)dataReader["nationality_id"] : 0));
                        result.Add("kebangsaan", nationality_id);
                       
                        this.CloseConnection();
                        return result;
                    }
                }
                this.CloseConnection();

            }
            return result;
        }
        
        private string getSexGender(int id)
        {
            if (id == 1)
                return "Laki-laki";
            return "Perempuan";
        }

        private string getNationality(int id)
        {
            if (id == 1)
                return "Indonesia";
            return "Asing";
        }

        private string getIDCard(int id)
        {
            if (id == 1)
                return "KTP";
            else if (id == 3)
                return "PASSPORT";
            return "SIM";
        }

        public List<List<string>> getSearchQuery(string key,int filter)
        {
            List<List<String>> result = new List<List<string>>();
            
            //yang penting --> nama,email,tempat lahir,nationality,sex_id,id_number,identity type,doc file,info,user document category,note
            switch (filter)
            {
                case FILTER_ALL :
                    List<long> idUser = getSearchQueryByTableUser(key);
                    int count = 1;
                    foreach (long id in idUser)
                    {
                        result.Add(getDataUser(id,count++));
                    }
                    break;
                case FILTER_BIRTHPLACE:
                    break;
                case FILTER_DOCFILE:
                    break;
                default :
                    break;
            }
            return result;

        }

        public List<List<string>> getPerpanjanganRating(string id,string licenseid,string licenselevelid)
        {
            List<List<string>> result = new List<List<string>>();
            string query = "SELECT" +
                              " u.name nama_user," +
                              " r.name nama_rating," +
                              " r.kode kode_rating," +
                              " ul.tgl_mulai_berlaku," +
                              " ul.tgl_berlaku_hingga " +
                            " FROM" +
                              " user_license_rating ur" +
                              " LEFT JOIN user_license ul ON (ur.user_license_id=ul.id)" +
                              " LEFT JOIN user u ON (ul.holder_user_id=u.id)" +
                              " LEFT JOIN rating r ON (ur.rating_id=r.id)" +
                            " WHERE u.id = '"+id+"' and ul.license_id = '"+licenseid+"' and ul.license_level_id = '"+licenselevelid+"'  ";
            if (this.OpenConnection())
            {
                MySqlCommand command = new MySqlCommand(query, connection);
                MySqlDataReader dataReader = command.ExecuteReader();
                if (dataReader != null)
                {
                    while (dataReader.Read())
                    {
                        string nama = dataReader["nama_user"].GetType() != typeof(DBNull) ? (string)dataReader["nama_user"] : "-";
                        string nama_rating = dataReader["nama_rating"].GetType() != typeof(DBNull) ? (string)dataReader["nama_rating"] : "-";
                        string kode_rating = dataReader["kode_rating"].GetType() != typeof(DBNull) ? (string)dataReader["kode_rating"] : "-";
                        string tgl_mulai_berlaku = dataReader["tgl_mulai_berlaku"].GetType() != typeof(DBNull) ? ((DateTime)dataReader["tgl_mulai_berlaku"]).ToShortDateString() : "-";
                        string tgl_berlaku_hingga = dataReader["tgl_berlaku_hingga"].GetType() != typeof(DBNull) ? ((DateTime)dataReader["tgl_berlaku_hingga"]).ToShortDateString() : "-";
                        string[] tempstring = new string[] { kode_rating,tgl_berlaku_hingga, nama, nama_rating, tgl_mulai_berlaku };
                        List<string> temp = new List<string>(tempstring);
                        result.Add(temp);                      
                    }                       
                }
                this.CloseConnection();   
            }
            return result;
        }

        public List<List<string>> getKlasifikasiRating(string id, string licenseid, string licenselevelid)
        {
            List<List<string>> result = new List<List<string>>();
            string query = "SELECT" +
                              " u.name nama_user," +
                              " r.name nama_rating," +
                              " r.kode kode_rating," +
                              " ul.tgl_mulai_berlaku," +
                              " ul.tgl_berlaku_hingga " +
                            " FROM" +
                              " user_license_rating ur" +
                              " LEFT JOIN user_license ul ON (ur.user_license_id=ul.id)" +
                              " LEFT JOIN user u ON (ul.holder_user_id=u.id)" +
                              " LEFT JOIN rating r ON (ur.rating_id=r.id)" +
                            " WHERE u.id = '" + id + "' and ul.license_id = '" + licenseid + "' and ul.license_level_id = '" + licenselevelid + "'  ";
            if (this.OpenConnection())
            {
                MySqlCommand command = new MySqlCommand(query, connection);
                MySqlDataReader dataReader = command.ExecuteReader();
                if (dataReader != null)
                {
                    while (dataReader.Read())
                    {
                        string nama = dataReader["nama_user"].GetType() != typeof(DBNull) ? (string)dataReader["nama_user"] : "-";
                        string nama_rating = dataReader["nama_rating"].GetType() != typeof(DBNull) ? (string)dataReader["nama_rating"] : "-";
                        string kode_rating = dataReader["kode_rating"].GetType() != typeof(DBNull) ? (string)dataReader["kode_rating"] : "-";
                        string tgl_mulai_berlaku = dataReader["tgl_mulai_berlaku"].GetType() != typeof(DBNull) ? ((DateTime)dataReader["tgl_mulai_berlaku"]).ToShortDateString() : "-";
                        string tgl_berlaku_hingga = dataReader["tgl_berlaku_hingga"].GetType() != typeof(DBNull) ? ((DateTime)dataReader["tgl_berlaku_hingga"]).ToShortDateString() : "-";
                        string[] tempstring = new string[] { kode_rating, tgl_mulai_berlaku, tgl_berlaku_hingga, nama, nama_rating };
                        List<string> temp = new List<string>(tempstring);
                        result.Add(temp);
                    }
                }
                this.CloseConnection();
            }
            return result;
        }

        public List<string> getCatatan(string id, string licenseid, string license_level_id)
        {
            List<string> result = new List<string>();
            string query = "SELECT " +
                              " un.note catatan" +
                            " FROM" +
                              " user_note un" +
                              " LEFT JOIN user u ON (un.user_id=u.id)" +
                              " LEFT JOIN user_license ul ON (un.user_license_id=ul.id)" +
                            " WHERE u.id ='"+id+"' OR (ul.license_id = '"+licenseid+"' and ul.license_level_id = '"+license_level_id+"')";
            if (this.OpenConnection())
            {
                MySqlCommand command = new MySqlCommand(query, connection);
                MySqlDataReader dataReader = command.ExecuteReader();
                if (dataReader != null)
                {
                    while (dataReader.Read())
                    {
                        string catatan = dataReader["catatan"].GetType() != typeof(DBNull) ? (string)dataReader["catatan"] : "-";
                        result.Add(catatan);
                        
                    }                    
                }
                this.CloseConnection();
            }
            return result;
        }

        public List<List<string>> getPerpanjanganLisensi(string licenseid, string license_level_id)
        {
            List<List<string>> result = new List<List<string>>();
            string query = "SELECT" +
                              " ulv.tgl_renewal tgl_perpanjang," +
                              " ulv.tgl_berlaku_mulai tgl_berlaku_mulai," +
                              " ulv.tgl_berlaku_hingga tgl_berlaku_hingga" +
                            " FROM" +
                              " user_license_term_validity ulv" +
                              " LEFT JOIN user_license ul ON (ulv.user_license_id=ul.id)" +
                            " WHERE ul.license_id = '"+licenseid+"' and ul.license_level_id = '"+license_level_id+"'";
            if (this.OpenConnection())
            {
                MySqlCommand command = new MySqlCommand(query, connection);
                MySqlDataReader dataReader = command.ExecuteReader();
                if (dataReader != null)
                {
                    while (dataReader.Read())
                    {
                        string tgl_renewal = dataReader["tgl_perpanjang"].GetType() != typeof(DBNull) ? ((DateTime)dataReader["tgl_perpanjang"]).ToShortDateString() : "-";
                        string tgl_berlaku_mulai = dataReader["tgl_berlaku_mulai"].GetType() != typeof(DBNull) ? ((DateTime)dataReader["tgl_berlaku_mulai"]).ToShortDateString() : "-";
                        string tgl_berlaku_hingga = dataReader["tgl_berlaku_hingga"].GetType() != typeof(DBNull) ? ((DateTime)dataReader["tgl_berlaku_hingga"]).ToShortDateString() : "-";
                        string[] tempstring = new string[] { tgl_renewal, tgl_berlaku_hingga,tgl_berlaku_mulai };
                        List<string> restemp = new List<string>(tempstring);
                        result.Add(restemp);
                    }                    
                }
                this.CloseConnection();
            }
            return result;
        }

        public List<string> getDirector()
        {
            List<string> result = new List<string>();
            string query = "SELECT d.name nama_direktur, d.nip nip_direktur FROM director d WHERE d.is_active = 1;";

            if (this.OpenConnection())
            {
                MySqlCommand command = new MySqlCommand(query, connection);
                MySqlDataReader dataReader = command.ExecuteReader();
                if (dataReader != null)
                {
                    while (dataReader.Read())
                    {
                        string nama_direktur = dataReader["nama_direktur"].GetType() != typeof(DBNull) ? (string)dataReader["nama_direktur"] : "-";
                        string nip_direktur = dataReader["nip_direktur"].GetType() != typeof(DBNull) ? (string)dataReader["nip_direktur"] : "-";
                        result.Add(nama_direktur);
                        result.Add(nip_direktur);

                        this.CloseConnection();
                        return result;
                        
                    }
                    
                }
                this.CloseConnection();
            }
            return result;
        }

        public List<List<string>> getLicense(string id)
        {
            List<List<string>> result = new List<List<string>>();

            string query = "SELECT license_id,license_level_id,"+
                "perpanjangan_print_count ppc,"+
                "klasifikasi_rating_print_count krpc,"+
                "rating_perpanjangan_print_count rppc,"+
                "comment_print_count cpc " +
                " FROM user_license WHERE holder_user_id='" + id + "'";

            if (this.OpenConnection())
            {
                MySqlCommand command = new MySqlCommand(query, connection);
                MySqlDataReader dataReader = command.ExecuteReader();
                if (dataReader != null)
                {
                    while (dataReader.Read())
                    {
                        string license_id = dataReader["license_id"].GetType() != typeof(DBNull) ? Convert.ToString((long)dataReader["license_id"]) : "0";
                        string license_level_id = dataReader["license_level_id"].GetType() != typeof(DBNull) ? Convert.ToString((long)dataReader["license_level_id"]) : "0";
                        int current_perpanjangan_print_index = (int)dataReader["ppc"];
                        int current_klasifikasi_rating_print_index = (int)dataReader["krpc"];
                        int current_perpanjangan_rating_print_index = (int)dataReader["rppc"];
                        int current_catatan_print_index = (int)dataReader["cpc"];

                        List<string> temp = new List<string>();
                        temp.Add(license_id);
                        temp.Add(license_level_id);
                        temp.Add(Convert.ToString(current_perpanjangan_print_index));
                        temp.Add(Convert.ToString(current_klasifikasi_rating_print_index));
                        temp.Add(Convert.ToString(current_perpanjangan_rating_print_index));
                        temp.Add(Convert.ToString(current_catatan_print_index));

                        result.Add(temp);
                    }
                    
                }
                this.CloseConnection();
            }
            return result;
        }

        public List<string> getLicenseDate(string id, string license_id, string license_level_id)
        {
            List<string> result = new List<string>();

            string query = "SELECT tgl_issue berlaku,tgl_berlaku_hingga hingga" +
                " FROM user_license WHERE holder_user_id='" + id + "'"+
                " AND license_id= '"+license_id+"' AND license_level_id='"+license_level_id+"' ";

            if (this.OpenConnection())
            {
                MySqlCommand command = new MySqlCommand(query, connection);
                MySqlDataReader dataReader = command.ExecuteReader();
                if (dataReader != null)
                {
                    while (dataReader.Read())
                    {
                        string tgl_berlaku = dataReader["berlaku"].GetType() != typeof(DBNull) ? ((DateTime)dataReader["berlaku"]).ToShortDateString() : "-";
                        string tgl_hingga = dataReader["hingga"].GetType() != typeof(DBNull) ? ((DateTime)dataReader["hingga"]).ToShortDateString() : "-";                        
                        result.Add(tgl_berlaku);
                        result.Add(tgl_hingga);
                        this.CloseConnection();
                        return result;
                    }

                }
                this.CloseConnection();
            }
            return result;
        }

        public string getLicenseName(string license_id)
        {
            string query = "SELECT name_id FROM license WHERE license_type_id='" + license_id + "'";
            if (this.OpenConnection())
            {
                MySqlCommand command = new MySqlCommand(query, connection);
                MySqlDataReader dataReader = command.ExecuteReader();
                if (dataReader != null)
                {
                    while (dataReader.Read())
                    {
                        string hasil = dataReader["name_id"].GetType() != typeof(DBNull) ? (string)dataReader["name_id"] : "undefined";
                        this.CloseConnection();
                        return hasil;
                    }

                }
                this.CloseConnection();
            }
            return "undefined"; 
        }

        public string getLicenseLevelName(string license_level_id)
        {
            string query = "SELECT name name_id FROM license_level WHERE license_id='" + license_level_id + "'";
            if (this.OpenConnection())
            {
                MySqlCommand command = new MySqlCommand(query, connection);
                MySqlDataReader dataReader = command.ExecuteReader();
                if (dataReader != null)
                {
                    while (dataReader.Read())
                    {
                        string hasil = dataReader["name_id"].GetType() != typeof(DBNull) ? (string)dataReader["name_id"] : "undefined";
                        this.CloseConnection();
                        return hasil;
                    }

                }
                this.CloseConnection();
            }
            return "undefined"; 
        }

        public void updatePrintIndexCount(string userid,string license_id,string license_level_id,int newIdx, int type)
        {
            string fieldname = "";
            switch (type)
            {
                case DBHelper.Perpanjangan_Index :
                    fieldname = "perpanjangan_print_count";
                    break;
                case DBHelper.Klasifikasi_Index :
                    fieldname = "klasifikasi_rating_print_count";
                    break;
                case DBHelper.Rating_Perpanjangan_Index :
                    fieldname = "rating_perpanjangan_print_count";
                    break;
                case DBHelper.Catatan_Index :
                    fieldname = "comment_print_count";
                    break;
                default :
                    break;
            }
            if (fieldname != "")
            {
                string query = "UPDATE user_license ul SET " + fieldname + "='" + newIdx + "'"+
                    " WHERE "+
                    " ul.holder_user_id='"+userid+"'"+
                    " AND ul.license_id='"+license_id+"'"+
                    " AND ul.license_level_id='"+license_level_id+"'";
                if (this.OpenConnection())
                {
                    MySqlCommand command = new MySqlCommand(query, connection);
                    command.ExecuteNonQuery();
                    this.CloseConnection();
                }                
            }
        }
    }
}
